﻿USE [ERP]
GO
/****** Object:  StoredProcedure [dbo].[a_UpdateAmix_1]    Script Date: 12/16/2009 23:06:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
 
 
 
 ALTER  PROCEDURE [dbo].[a_UpdateAmix_1] 
   	@An int,
   	@luna int, 
   	@CodDiagrama varchar (2000), 
   	@ret_code int output
   AS
  
   begin
  	declare @ern as int
  	declare @sirComanda as nvarchar(4000)
  
   	declare @sAn as varchar(4)
   	set @sAn=cast(@an as varchar)
  
   	declare @sLuna as varchar(2)
   	set @sLuna=cast(@Luna as varchar)
   	set @sluna=case when len(@sluna)=1 then '0'+@sLuna else @sLuna end
  
   	declare @TableName as varchar(1000)
  	 set @TableName=
  	 '[dbo].[a_'+@sAn+@sLuna+'a_arh_MIX]'
  
   	begin transaction
   --modificare stare mijloace fixe luate in evidenta
  set @ern=101
  set @sirComanda=
   	'UPDATE a_act_mix set
  		AmoLn_R = RA,
   		CONS = ''E''
   	WHERE CodDiagrama in (' + @CodDiagrama + ' ) and CONS = ''I''
 --  		AND EXISTS (SELECT * FROM a_act_mis WHERE CodDiagrama = a_act_mix.CodDiagrama 
 --  		            AND NrInv = a_act_mix.NrInv 
 -- 				AND coalesce(year(DataDoc), ' + convert(varchar(4), @An) + ') = ' + convert(varchar(4), @An) + '
 --  				AND coalesce(month(DataDoc), ' + rtrim(ltrim(convert(varchar(2), @Luna))) + ') = ' + rtrim(ltrim(convert(varchar(2), @Luna))) + ')'
 --  
  
  print @sirComanda 
  exec sp_executesql @sirComanda
    if (@@error<>0) goto herr
  
  -- modificare amortizare lunara mijloace iesite sau conservate
  set @ern = 102
  set @sirComanda =
   	'UPDATE a_act_mix SET
   		AmoLn_R = 0
   	WHERE CodDiagrama in (' + @CodDiagrama + ') and (CONS = ''O'' OR CONS = ''C'')
 --  		AND EXISTS (SELECT * FROM a_act_mis WHERE CodDiagrama = a_act_mix.CodDiagrama
 --  			AND NrInv = a_act_mix.NrInv AND coalesce(year(DataDoc), ' + convert(varchar(4), @An) + ') = ' + convert(varchar(4), @An) + '
 --  				AND coalesce(month(DataDoc), ' + rtrim(ltrim(convert(varchar(2), @Luna))) + ') = ' + rtrim(ltrim(convert(varchar(2), @Luna))) + ')'
 --  
  
  print @sirComanda 
   exec sp_executesql @sirComanda
    if (@@error<>0) goto herr
  
   --calcul amorizare
  	-- actualizez amortizarea lunara curente
  -- -- set @ern=103
  -- -- set @sirComanda=
  -- -- 		'update a_act_mix set
  -- -- 			AmoLn=RA
  -- -- 		where CodDiagrama in ( '+ @CodDiagrama+' ) and cons=''E'''
  -- -- print @sirComanda 
  -- --  exec sp_executesql @sirComanda
  -- --   if (@@error<>0) goto herr
  
  	-- pentru regim accelet dupa primul an se trece la regim liniar
  
  set @ern=104
  set @sirComanda=
  		'update a_act_mix set
  			AMOAN = VALinv / 2 / (durn-1),			--rata anuala 
  			RA = Round(VALinv / 2 /(durn-1) / 12, 2) 	--rata lunara
  		where CodDiagrama in ( ' + @CodDiagrama + ' ) and cons = ''E'' 
 			and regim = 3 and durcluni < (durn-1)*12'
   print @sirComanda 
  exec sp_executesql @sirComanda
    if (@@error<>0) goto herr
  
  --actualizare stare mijloc fix mijloace fixe amortizate -> Amortizarea lunara pe zero
  set @ern=106
  set @sirComanda=
   	'UPDATE a_act_mix set
  		AmoLn_A = AmoLn_R,
   		CONS = ''A'',
  		AmoLn_R = 0
   	where CodDiagrama in (' + @CodDiagrama + ' ) and VALR = 0  and CONS <> ''O'''
   exec sp_executesql @sirComanda
    if (@@error<>0) goto herr
  
   	-- modificare valoare(tip=6) si modernizari(tip=7)
 --/ *Geta
   set @ern = 700
   set @sirComanda = 'UPDATE a_act_mix SET AL = (SELECT Tip FROM a_act_mis WHERE a_act_mix.CodDiagrama = a_act_mis.CodDiagrama
 						AND a_act_mix.NrInv = a_act_mis.NrInv and a_act_mis.TIP in (''6'', ''7''))
 			WHERE EXISTS (SELECT * FROM a_act_mis WHERE a_act_mix.CodDiagrama = a_act_mis.CodDiagrama
 						AND CONS <> ''A'' AND a_act_mix.NrInv = a_act_mis.NrInv and a_act_mis.TIP in (''6'', ''7''))
 			AND CodDiagrama in (' + @CodDiagrama + ')'  
   print @sirComanda 
   exec sp_executesql @sirComanda
   if (@@error<>0) goto herr
 
 
 
   set @ern = 701
   set @sirComanda = 'UPDATE a_act_mix SET DurcLuni = (SELECT coalesce(DurN, 0) 
 						FROM a_act_mis WHERE a_act_mix.CodDiagrama = a_act_mis.CodDiagrama
 						AND a_act_mix.NrInv = a_act_mis.NrInv and a_act_mix.AL = a_act_mis.Tip)
 			WHERE EXISTS (SELECT * FROM a_act_mis WHERE a_act_mix.CodDiagrama = a_act_mis.CodDiagrama
 						AND a_act_mix.NrInv = a_act_mis.NrInv and a_act_mix.AL = a_act_mis.Tip) 
 					AND a_act_mix.AL in (''6'', ''7'') AND CONS <> ''A'' AND CodDiagrama in (' + @CodDiagrama + ')'  
   print @sirComanda 
   exec sp_executesql @sirComanda
   if (@@error<>0) goto herr
 
 
 
   set @ern = 702
   set @sirComanda = 'UPDATE a_act_mix SET ValInv = ValInv + (SELECT coalesce(ValInv, 0) FROM a_act_mis WHERE a_act_mix.CodDiagrama = a_act_mis.CodDiagrama
 						AND a_act_mix.NrInv = a_act_mis.NrInv and a_act_mix.AL = a_act_mis.Tip)
 			WHERE EXISTS (SELECT * FROM a_act_mis WHERE a_act_mix.CodDiagrama = a_act_mis.CodDiagrama
 						AND a_act_mix.NrInv = a_act_mis.NrInv and a_act_mix.AL = a_act_mis.Tip)
 			AND a_act_mix.AL in (''6'', ''7'') AND CONS <> ''A'' AND CodDiagrama in (' + @CodDiagrama + ')' 
   print @sirComanda 
   exec sp_executesql @sirComanda
   if (@@error<>0) goto herr
 
   set @ern = 704
   set @sirComanda = 'UPDATE a_act_mix SET VALA = ValA + RA
 			WHERE EXISTS (SELECT * FROM a_act_mis WHERE a_act_mix.CodDiagrama = a_act_mis.CodDiagrama
 				AND a_act_mix.NrInv = a_act_mis.NrInv and a_act_mix.AL = a_act_mis.Tip)
 				AND a_act_mix.AL in (''6'', ''7'') AND CONS <> ''A'' and a_act_mix.CodDiagrama in (' + @CodDiagrama + ')'  
   print @sirComanda 
   exec sp_executesql @sirComanda
   if (@@error<>0) goto herr
 
 
   set @ern = 703
   set @sirComanda = 'UPDATE a_act_mix SET RA = (SELECT AmoLn FROM a_act_mis where a_act_mix.CodDiagrama = a_act_mis.CodDiagrama
 				AND a_act_mix.NrInv = a_act_mis.NrInv and a_act_mix.AL = a_act_mis.Tip)
 			WHERE EXISTS (SELECT * FROM a_act_mis WHERE a_act_mix.CodDiagrama = a_act_mis.CodDiagrama
 				AND a_act_mix.NrInv = a_act_mis.NrInv and a_act_mix.AL = a_act_mis.Tip)
 				AND a_act_mix.AL in (''6'', ''7'') AND CONS <> ''A'' and a_act_mix.CodDiagrama in (' + @CodDiagrama + ')'  
   print @sirComanda 
   exec sp_executesql @sirComanda
   if (@@error<>0) goto herr
 
 
 
   set @ern = 705
   set @sirComanda = 'UPDATE a_act_mix SET AmoAn = 12 * RA
 			WHERE EXISTS (SELECT * FROM a_act_mis WHERE a_act_mix.CodDiagrama = a_act_mis.CodDiagrama
 				AND a_act_mix.NrInv = a_act_mis.NrInv and a_act_mix.AL = a_act_mis.Tip)
 				AND a_act_mix.AL in (''6'', ''7'') AND CONS <> ''A'' and a_act_mix.CodDiagrama in (' + @CodDiagrama + ')'  
   print @sirComanda 
   exec sp_executesql @sirComanda
   if (@@error<>0) goto herr
 
 
   set @ern = 706
   set @sirComanda = 'UPDATE a_act_mix SET ValR = coalesce(ValInv, 0) - coalesce(ValA, 0) /*+ (SELECT coalesce(ValInv, 0) FROM a_act_mis WHERE a_act_mix.CodDiagrama = a_act_mis.CodDiagrama
 						AND a_act_mix.NrInv = a_act_mis.NrInv and a_act_mix.AL = a_act_mis.Tip)*/
 			WHERE EXISTS (SELECT * FROM a_act_mis WHERE a_act_mix.CodDiagrama = a_act_mis.CodDiagrama
 						AND a_act_mix.NrInv = a_act_mis.NrInv and a_act_mix.AL = a_act_mis.Tip)
 			AND a_act_mix.AL in (''6'', ''7'') AND CONS <> ''A'' AND a_act_mix.CodDiagrama in (' + @CodDiagrama + ')'  
   print @sirComanda 
   exec sp_executesql @sirComanda
   if (@@error<>0) goto herr
 
 -- Geta */
 
 --   set @ern=112
 --   set @sirComanda=
 --    	'update a_act_mix set
 --    		al = mis.al,
 --    		valinv = valinv + mis.ValMod,
 --    		valr = valr+mis.ValMod,
 --    		durcluni = durcluni + case  when mis.al = ''6'' then 0 else coalesce(mis.durn,0) end,
 --     		ra = round((valr+mis.ValMod)/(durcluni+case  when mis.al=''6'' then 0 else coalesce(mis.durn,0) end),2),
 --  --   		AmoLn_R = round((valr + mis.ValMod) / (durcluni + case  when mis.al=''6'' then 0 else coalesce(mis.durn,0) end),2),
 --     		amoan = (valr + mis.ValMod) / (durcluni + case when mis.al=''6'' then 0 else coalesce(mis.durn,0) end) * 12
 --    	from
 --    		(select nrinv, tip as al, valinv as ValMod, durn
 --    		from a_act_mis where (tip=''6'' or tip=''7'') and CodDiagrama in ( '+ @CodDiagrama+' )) mis
 --    	where a_act_mix.CodDiagrama in ( ' + @CodDiagrama + ' ) and a_act_mix.nrinv = mis.nrinv and a_act_mix.durcluni > 0'  
 --   
 --    print @sirComanda 
 --   exec sp_executesql @sirComanda
 --     if (@@error<>0) goto herr
 --  
   	-- modificare valoare(tip=6) si modernizari(tip=7)<<< mijloace deja amortizate >>>
   set @ern=992
   set @sirComanda=
    	'update a_act_mix set
  		cons = ''E'',
    		al = mis.al,
    		valinv = valinv + mis.ValMod,
    		valr = mis.ValMod,
    		durcluni = coalesce(mis.durn, 0),
     		ra = round((mis.ValMod)/(coalesce(mis.durn, 1)), 2),
     		amoan = (mis.ValMod)/(coalesce(mis.durn, 1)) * 12
    	from
    		(select nrinv, tip as al, valinv as ValMod, durn
    		from a_act_mis where (tip = ''6'' or tip = ''7'') and CodDiagrama in ( ' + @CodDiagrama + ' )
 			and coalesce(year(DataDoc), ' + convert(varchar(4), @AN) + ') = ' + convert(varchar(4), @AN) + '
 			and coalesce(month(DataDoc), ' + rtrim(ltrim(convert(varchar(2), @Luna))) + ') = ' + rtrim(ltrim(convert(varchar(2), @Luna))) + ') mis
    	where a_act_mix.CodDiagrama in ( ' + @CodDiagrama + ' ) and a_act_mix.nrinv = mis.nrinv and a_act_mix.cons = ''A'''  
   
   print @sirComanda 
   exec sp_executesql @sirComanda
     if (@@error<>0) goto herr
  
  	-- calcul amortizare lunara
  set @ern=105
  set @sirComanda=
  	 	'update a_act_mix set
  	 		VALA = VALA + case when RA < VALR then RA else VALR end ,
  	 		VALR = VALR - case when RA < VALR then RA else VALR end ,
  	 		DURCLUNI = Durcluni - 1
 			,AmoLn_A = case when RA < VALR then RA else VALR end	--Geta am pus, pt ca nu calcula in ultima luna
  	 	where CodDiagrama in ( '+ @CodDiagrama+' ) and cons = ''E''
 		and not exists (select * from a_act_mis where tip in (6, 7) and CodDiagrama = a_act_mix.CodDiagrama and nrInv = a_act_mix.NrInv 
 			and coalesce(year(DataDoc), ' + convert(varchar(4), @AN) + ') = ' + convert(varchar(4), @AN) + '
 			and coalesce(month(DataDoc), ' + rtrim(ltrim(convert(varchar(2), @Luna))) + ') = ' + rtrim(ltrim(convert(varchar(2), @Luna))) + ')'
  print @sirComanda 
   exec sp_executesql @sirComanda
    if (@@error<>0) goto herr
  
  set @ern=117
  set @sirComanda=
  	 	'update a_act_mix set
  			--AmoLn_A = AmoLn_R,
  			AmoLn_R = case when RA < VALR then RA else VALR end
  	 	where CodDiagrama in ( '+ @CodDiagrama+' ) and cons = ''E'''
  print @sirComanda 
   exec sp_executesql @sirComanda
    if (@@error<>0) goto herr
  
  	-- calcul amortizare lunara viitoare
  set @ern=115
  set @sirComanda=
  	 	'update a_act_mix set
  	 		VALA_DUPA = VALA + amoln
  	 	where CodDiagrama in ( ' + @CodDiagrama + ' ) and cons=''E'''
  print @sirComanda 
   exec sp_executesql @sirComanda
    if (@@error<>0) goto herr
  
  
   --actualizare stare mijloc fix mijloace fixe amortizate->Marcare stare amortizata
  -- -- set @ern=116
  -- -- set @sirComanda=
  -- --  	'update a_act_mix set
  -- -- 		AmoLn_A=AmoLn,
  -- --  		CONS=''A'',
  -- -- 		AmoLn=0
  -- --  	where CodDiagrama in ( '+ @CodDiagrama+' ) and VALR=0  and CONS <> ''O'''
  -- -- print @sirComanda 
  -- --  exec sp_executesql @sirComanda
  -- --   if (@@error<>0) goto herr
  
   --actualizare miscari
   	-- intrare mijloace fixe noi
 
 
 
 --a_act_mix.CONS  [I intrat, si la care se va aplica amortizare la inchiderea lunii]
 --a_act_mix.AmoLn [Amortizarea lunara]
 
  set @ern=107
  set @sirComanda=
   	'insert into a_act_mix  (AL, CONS, NRINV, DEN, GRUPA, SUBGRUPA, CLASA, SUBCLASA, FAMILIA, 
   				SEC, ATEL, CONTS, CONTAN1, CONTAN2, ANI, LNI, ANO, LNO, DURN, DURCANI, 
  				DURCLUNI, NA, DOCI, DOCO, VALINV, RA, VALA, VALR, VALIA, AMOAN, 
  				COTA, KMP, REGIM, REGLD, ULTAMOAN, UTIL, INDICE, VALUTL, 
          VALUTA, VALUTC, CodDiagrama, AmoLn, AmoLn_A)
   		select  0,''I'', NRINV, DEN, GRUPA, SUBGRUPA, CLASA, SUBCLASA, FAMILIA,
   		        SECI, ATELI, CONTS, CONTAN1, CONTAN2,
 			coalesce(year(DataDoc), ' + convert(varchar(4), @AN) + ') AnI, 
 			coalesce(month(DataDoc), ' + rtrim(ltrim(convert(varchar(2), @Luna))) + '), 
   		 	NULL, NULL, DURN, DURN  as durcani,
  			case when coalesce(CDEL, '''') = ''T'' then 12*(valinv-valutc)/ra else DURN*12 end  as durcluni,
  			NA, DOCI, null, VALINV, RA,
  			case when coalesce(CDEL, '''') = ''T'' then valutc else 0 end as vala,
  			case when coalesce(CDEL, '''') = ''T'' then valinv - valutc else VALINV end as valr, NULL,
   			VALA as amoan, Cota, null,REGIM,null,null,null,null,null,
   			null,null, CodDiagrama, ra , 0
   		FROM a_act_mis WHERE CodDiagrama in (' + @CodDiagrama + ') 
 			and Tip = ''1'' and coalesce(CDEL, '''') <> ''T''
 			and coalesce(year(DataDoc), ' + convert(varchar(4), @An) + ') = ' + convert(varchar(4), @An) + 
 			' and coalesce(month(DataDoc), ' + rtrim(ltrim(convert(varchar(2), @Luna))) + ') = ' + rtrim(ltrim(convert(varchar(2), @Luna)))
  
  print @sirComanda 
   exec sp_executesql @sirComanda
    if (@@error<>0) goto herr
   
  
   	-- iesire mijloace fixe
  set @ern=108
  set @sirComanda=
   	'update a_act_mix set
   		al = mis.al,
   		ano = mis.ano,
   		lno = mis.lno,
   		doco = mis.doco,
  		AmoLn_R = 0,
   		cons = ''O''	
   	from
   		(select nrinv, doco ,
   			(select distinct AnDeLucru from cfgamix where  CodNod in ( '+ @CodDiagrama+' )) as ano,
   			(select distinct LunaDeLucru from cfgamix where  CodNod in ( '+ @CodDiagrama+' )) as lno,
   			2 as al
   		from a_act_mis where tip = ''2'' and CodDiagrama in ('+ @CodDiagrama + ')  and 
 		coalesce(year(DataDoc), ' + convert(varchar(4), @An) + ') = ' + convert(varchar(4), @An) + 
 		' and coalesce(month(DataDoc), ' + rtrim(ltrim(convert(varchar(2), @Luna))) + ') = ' + rtrim(ltrim(convert(varchar(2), @Luna)))+ ') mis
   	where a_act_mix.CodDiagrama in ('+ @CodDiagrama + ') and a_act_mix.nrinv = mis.nrinv'
  
  print @sirComanda 
   exec sp_executesql @sirComanda
    if (@@error<>0) goto herr
  
   
   	-- transfer intern
  set @ern=109
  set @sirComanda=
   	'update a_act_mix set
   		sec=mis.sec,
   		atel=mis.atel
   	from
   		(select nrinv,seco as sec,atelo as atel
   		from a_act_mis where tip = ''3'' and CodDiagrama in (' + @CodDiagrama + ')) mis
   	where a_act_mix.CodDiagrama in (' + @CodDiagrama + ') and a_act_mix.nrinv = mis.nrinv'
  
   
  print @sirComanda 
   exec sp_executesql @sirComanda
    if (@@error<>0) goto herr
  
   	-- conservare
  set @ern=110
  set @sirComanda=
   	'update a_act_mix set
   		al = mis.al,
   		cons = mis.cons,
  		AmoLn_R = 0
   	from
   		(select nrinv,5 as al,''C'' as cons,
   			(select AnDeLucru from cfgamix where  CodNod in ( '+ @CodDiagrama+' )) as ano,
   			(select LunaDeLucru from cfgamix where  CodNod in ( '+ @CodDiagrama+' )) as lno
   		from a_act_mis where tip=''4'' and CodDiagrama in ( '+ @CodDiagrama+' )) mis
   	where a_act_mix.CodDiagrama in ( '+ @CodDiagrama+' ) and a_act_mix.nrinv=mis.nrinv'
   
  print @sirComanda 
   exec sp_executesql @sirComanda
    if (@@error<>0) goto herr
  
   
   	-- reactivare
  set @ern=111
  set @sirComanda=
   	'update a_act_mix set
   		al = mis.al,
   		cons=mis.cons
   	from
   		(select nrinv,5 as al,''E'' as cons,
   			(select distinct AnDeLucru from cfgamix where  CodNod in ( '+ @CodDiagrama+' )) as ano,
   			(select distinct LunaDeLucru from cfgamix where  CodNod in ( '+ @CodDiagrama+' )) as lno
   		from a_act_mis where tip=''5'' and CodDiagrama in ( '+ @CodDiagrama+' )) mis
   	where a_act_mix.CodDiagrama in ( '+ @CodDiagrama+' ) and a_act_mix.nrinv=mis.nrinv'
   
  
  print @sirComanda 
   exec sp_executesql @sirComanda
    if (@@error<>0) goto herr
   
  -- --  	-- modificare valoare(tip=6) si modernizari(tip=7)
  -- --  set @ern=112
  -- --  set @sirComanda=
  -- --   	'update a_act_mix set
  -- --   		al=mis.al,
  -- --   		valinv=valinv +mis.ValMod,
  -- --   		valr=valr+mis.ValMod,
  -- --   		durcluni=durcluni+case  when mis.al=''6'' then 0 else coalesce(mis.durn,0) end,
  -- --    		ra=round((valr+mis.ValMod)/(durcluni+case  when mis.al=''6'' then 0 else coalesce(mis.durn,0) end)+0.005,2),
  -- -- --   		AmoLn_R=round((valr+mis.ValMod)/(durcluni+case  when mis.al=''6'' then 0 else coalesce(mis.durn,0) end)+0.005,2),
  -- --    		amoan=(valr+mis.ValMod)/(durcluni+case  when mis.al=''6'' then 0 else coalesce(mis.durn,0) end)*12
  -- --   	from
  -- --   		(select nrinv,tip as al,valinv as ValMod,durn
  -- --   		from a_act_mis where (tip=''6'' or tip=''7'') and CodDiagrama in ( '+ @CodDiagrama+' )) mis
  -- --   	where a_act_mix.CodDiagrama in ( '+ @CodDiagrama+' ) and a_act_mix.nrinv=mis.nrinv and a_act_mix.durcluni>0'  
  -- --  
  -- --   print @sirComanda 
  -- --  exec sp_executesql @sirComanda
  -- --    if (@@error<>0) goto herr
   
   	-- parcurs lunar
   
  
  -- stergere miscari
  set @ern=113
  set @sirComanda=
   	'delete a_act_mis where a_act_mis.CodDiagrama in ( '+ @CodDiagrama+' ) and coalesce(year(DataDoc), ' + convert(varchar(4), @An) + ') = ' + convert(varchar(4), @An) +
  		' AND coalesce(month(DataDoc), ' + rtrim(ltrim(convert(varchar(2), @Luna))) + ') = ' + rtrim(ltrim(convert(varchar(2), @Luna)))
  
   print @sirComanda 
  exec sp_executesql @sirComanda
    if (@@error<>0) goto herr
  
   --actualizare AMIX (avansez luna si anul daca s-a depasit luna 12)
  set @ern=114
  set @sirComanda=
   	'update cfgAmix set 
   		LunaDeLucru=case when LunaDeLucru = 12 then 1 else LunaDeLucru+1 end,
   		AnDeLucru=case when LunaDeLucru = 12 then AnDeLucru+1 else AnDeLucru end,
   		Status=0
   	where CodNod in ( '+ @CodDiagrama+' )'
  print @sirComanda 
   exec sp_executesql @sirComanda
    if (@@error<>0) goto herr
   
   --actualizare a_arh____MIX (Fac AmoLn sa fie AmoLn_R Dupa Calcul)
  set @ern=120
  set @sirComanda=
   	'update ' + @TableName + ' set 
  		AmoLn = a_act_mix.AmoLn_R
  	from a_act_mix
   	where ' + @TableName + '.CodDiagrama in ( '+ @CodDiagrama+' ) and a_act_mix.NrInv='+@TableName+'.NrInv '
  print @sirComanda 
   exec sp_executesql @sirComanda
    if (@@error<>0) goto herr
  
   	commit transaction
   	select  @ret_code=1
   	return
   herr: rollback transaction
         select  @ret_code=@ern
         return
   end
 
 
 
------------------------------
'insert into a_act_mix  (AL, CONS, NRINV, DEN, GRUPA, SUBGRUPA, CLASA, SUBCLASA, FAMILIA, 
    				SEC, ATEL, CONTS, CONTAN1, CONTAN2, 
    				ANI, 
    				LNI, 
    				ANO, LNO, DURN, 
           DURCANI, 
   				DURCLUNI, 
   				NA, DOCI, DOCO, VALINV, RA, 
   				VALA, 
   				VALR, VALIA, 
    				AMOAN, 
   				COTA, KMP, REGIM, REGLD, ULTAMOAN, UTIL, INDICE, VALUTL, 
           VALUTA, VALUTC, CodDiagrama, AmoLn, AmoLn_A)
    		select  9,''E'',NRINV, DEN, GRUPA, SUBGRUPA, CLASA, SUBCLASA, FAMILIA,
    		        SECI, ATELI, CONTS, CONTAN1, CONTAN2,
    			(select distinct AnDeLucru from cfgamix where  CodNod in ( '+ @CodDiagrama+' )) as ano,
    			(select distinct LunaDeLucru from cfgamix where  CodNod in ( '+ @CodDiagrama+' )) as lno,
    			null,null,DURN,
    			DURN  as durcani,
   			case when coalesce(CDEL, '''') = ''T'' then case when ra <> 0 then 12*(valinv-valutc)/ra else 0 end
 						else DURN*12 end  as durcluni,
   			NA, DOCI, null, VALINV, RA,
   			case when coalesce(CDEL, '''') = ''T'' then valutc  else 0 end as vala,
   			case when coalesce(CDEL, '''') = ''T'' then valinv-valutc else VALINV end as valr, NULL,
    			VALA as amoan,
   			Cota,null,REGIM,null,null,null,null,null,
    			null,null, CodDiagrama, coalesce(ra, 0), 0
    		from a_act_mis where CodDiagrama in ( ' + @CodDiagrama + ' ) 
 			and Tip=''1'' and coalesce(CDEL, '''') = ''T''
 			 and not exists (select * from a_act_mix where CodDiagrama = a_act_mis.CodDiagrama and NrInv = a_act_mis.NrInv)
  			and coalesce(year(DataDoc), ' + convert(varchar(4), @An) + ') = ' + convert(varchar(4), @An) + 
  			' and coalesce(month(DataDoc), ' + rtrim(ltrim(convert(varchar(2), @Luna))) + ') = ' + rtrim(ltrim(convert(varchar(2), @Luna)))
 